﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DatabaseHelper;
using StaticFunction;
using Log;
using Table;
using System.Data;

namespace DataAccess
{
    public class cDA_Voucher
    {
        public const string m_msg_head = "Voucher";

        #region "Change Expire Date"

        public  int ExistVoucherData(string _VoucherText)
        {
            int result = 0 ;

            var query = new StringBuilder();
            query.AppendLine("SELECT count(*) as VoucherQty FROM tblStock");
            query.AppendLine("LEFT JOIN tblVoucherType ON tblVoucherType.lID = tblStock.lVoucherTypeID");
            query.AppendLine("WHERE nVoucherCode = @VoucherBarcode");
            query.AppendLine("AND dIssuedDate > '1900-01-01 00:00:00.000'");

            using (var db = new DbHelper())
            {
                try
                {
                    db.AddParameter("@VoucherBarcode", _VoucherText);
                    result = SF.GetInt32(db.ExecuteScalar(query.ToString()));
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return result;
        }

        public int ExistVoucherDataCheckDate(string _VoucherText, string _dExpireDate)
        {
            int result = 0;

            var query = new StringBuilder();
            query.AppendLine("SELECT count(*) as VoucherQty FROM tblStock");
            query.AppendLine("LEFT JOIN tblVoucherType ON tblVoucherType.lID = tblStock.lVoucherTypeID");
            query.AppendLine("WHERE nVoucherCode = @VoucherBarcode  AND @dExpiryDate < convert(varchar, GETDATE(), 121)");
            //query.AppendLine("AND dIssuedDate > '1900-01-01 00:00:00.000'");

            using (var db = new DbHelper())
            {
                try
                {
                    db.AddParameter("@VoucherBarcode", _VoucherText);
                    db.AddParameter("@dExpiryDate", _dExpireDate);
                    result = SF.GetInt32(db.ExecuteScalar(query.ToString()));
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return result;
        }

        public int ExistVoucherDataCheckDatePeroid(string _VoucherText, string _dExpireDate)
        {
            int result = 0;

            var query = new StringBuilder();
            query.AppendLine("SELECT count(*) as VoucherQty FROM tblStock");
            query.AppendLine("LEFT JOIN tblVoucherType ON tblVoucherType.lID = tblStock.lVoucherTypeID");
            query.AppendLine("WHERE nVoucherCode = @VoucherBarcode  AND dExpiryDate > convert(varchar, GETDATE(), 121)");
            //query.AppendLine("AND dIssuedDate > '1900-01-01 00:00:00.000'");

            using (var db = new DbHelper())
            {
                try
                {
                    db.AddParameter("@VoucherBarcode", _VoucherText);
                    db.AddParameter("@dExpiryDate", _dExpireDate);
                    result = SF.GetInt32(db.ExecuteScalar(query.ToString()));
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return result;
        }
        public tbl_Voucher GetDataVoucherData_ByVoucherBarcode(string _VoucherBarcode)
        {
            tbl_Voucher result = new tbl_Voucher();
            string query = "SELECT DISTINCT tblVoucherType.nVoucherCode,tblVoucherType.sDescription FROM tblStock";
            query += " LEFT JOIN tblVoucherType ON tblVoucherType.lID = tblStock.lVoucherTypeID";
            query += " WHERE nVoucherCode = @nVoucherCode";
            query += " AND dIssuedDate > '1900-01-01 00:00:00.000'";
            using (var db = new DbHelper())
            {
                try
                {
                    db.AddParameter("@nVoucherCode", _VoucherBarcode);
                    var dt = db.ExecuteDataSet(query).Tables[0];

                    if (dt != null && dt.Rows.Count > 0)
                    {
                        result.nVoucherCode = SF.GetString(dt.Rows[0]["nVoucherCode"]);
                        result.sDescription = SF.GetString(dt.Rows[0]["sDescription"]);
                       
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return result;
        }

        public  bool UpdateVoucherDataAllVoucher_ByID(string _VoucherBarcode, string _ExpireDate, string _ReqNo, string _UserNo)
        {
            bool result = true;
            string query = "UPDATE tblStock SET";
            query += " dExpiryDate  ='" + _ExpireDate + "'";
            query += " FROM tblStock LEFT JOIN tblVoucherType ON tblVoucherType.lID = tblStock.lVoucherTypeID";
            query += " WHERE nVoucherCode = '" + _VoucherBarcode+"'";

            var query_log = new StringBuilder();
            using (var db = new DbHelper())
            {
                query_log.Append("INSERT INTO tblManualVoucherLog (Req_No, User_No,FunctionName,Script)");
                query_log.Append(" VALUES (@ReqNo,null,@FunctionName,@Script)");
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@ReqNo", _ReqNo);
                   // db.AddParameter("@UserNo", null);
                    db.AddParameter("@FunctionName", "ChangeExpireDate");
                    db.AddParameter("@Script", query);

                    db.ExecuteNonQuery(query, DbConnectionState.KeepOpen);

                    db.ExecuteNonQuery(query_log.ToString(), DbConnectionState.KeepOpen);

                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public  bool UpdateVoucherDataPeriod_ByID(string _VoucherBarcode, string _ExpireDate, string _FromBarcode, string _ToBarcode, string _ReqNo, string _UserNo)
        {
            bool result = true;

            string query = "UPDATE tblStock SET";
            query += " dExpiryDate  ='" + _ExpireDate + "'";
            query += " FROM tblStock LEFT JOIN tblVoucherType ON tblVoucherType.lID = tblStock.lVoucherTypeID";
            query += " WHERE nVoucherCode = '" + _VoucherBarcode+"'";
            query += " AND tblStock.lVoucherNumber BETWEEN RIGHT(" + _FromBarcode + ",7) AND RIGHT(" + _ToBarcode + ",7)";

            var query_log = new StringBuilder();
            using (var db = new DbHelper())
            {
                query_log.Append("INSERT INTO tblManualVoucherLog (Req_No, User_No,FunctionName,Script)");
                query_log.Append(" VALUES (@ReqNo,null,@FunctionName,@Script)");
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@ReqNo", _ReqNo);
                    //db.AddParameter("@UserNo", _UserNo);
                    db.AddParameter("@FunctionName", "ChangeExpireDate");
                    db.AddParameter("@Script", query);
                    db.ExecuteNonQuery(query, DbConnectionState.KeepOpen);
                    db.ExecuteNonQuery(query_log.ToString (), DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public  bool InsertLog_ManualVoucherLog(string _Req_No, string _User_No, string _FunctionName, string _Script)
        {
            bool result = true;
            var query = new StringBuilder();
            query.Append("INSERT INTO tblManualVoucherLog (Req_No,User_No,FunctionName,Script)");
            query.Append(" VALUES (@Req_No,null,@FunctionName,@Script)");

            using (var db = new DbHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@Req_No", _Req_No);
                   // db.AddParameter("@User_No", _User_No);
                    db.AddParameter("@FunctionName", _FunctionName);
                    db.AddParameter("@Script", _Script);
                    db.ExecuteNonQuery(query.ToString(), DbConnectionState.KeepOpen);

                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }

        #endregion

        #region "Change Issue Price"
        public  DataTable GetVoucherPriceData(string _VoucherText)
        {

            var result = new DataTable();
            string query = "SELECT tblVoucherType.nVoucherCode,tblVoucherPrice.mIssuePrice ,tblVoucherType.sDescription ";
            query += " FROM tblVoucherPrice INNER JOIN tblVoucherType";
            query += " ON tblVoucherType.lID = tblVoucherPrice.lVoucherTypeID";
            query += " WHERE tblVoucherType.nVoucherCode ='" + _VoucherText+"'";
            using (var db = new DbHelper())
            {
                try
                {
                    result = db.ExecuteDataSet(query).Tables[0];
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0006", ex));
                }
            }
            return result;
        }
        public tbl_Voucher GetDataVoucherDataIssuePrice_ByVoucherBarcode(string _VoucherBarcode)
        {
            tbl_Voucher result = new tbl_Voucher();
            string query = "SELECT tblVoucherType.nVoucherCode,tblVoucherPrice.mIssuePrice ,tblVoucherType.sDescription ";
            query += " FROM tblVoucherPrice INNER JOIN tblVoucherType";
            query += " ON tblVoucherType.lID = tblVoucherPrice.lVoucherTypeID";
            query += " WHERE tblVoucherType.nVoucherCode ='" + _VoucherBarcode+"'";
            using (var db = new DbHelper())
            {
                try
                {
                    db.AddParameter("@nVoucherCode", _VoucherBarcode);
                    var dt = db.ExecuteDataSet(query).Tables[0];

                    if (dt != null && dt.Rows.Count > 0)
                    {
                        result.nVoucherCode = SF.GetString(dt.Rows[0]["nVoucherCode"]);
                        result.sDescription = SF.GetString(dt.Rows[0]["sDescription"]);
                        result.current_VoucherPrice = SF.GetString(dt.Rows[0]["mIssuePrice"]);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0007", ex));
                }
            }
            return result;
        }
        public  bool UpdateVoucherPriceData_ByID(string _VoucherBarcode, string _New_VoucherPrice, string _ReqNo, string _UserNo)
        {
            bool result = true;
            string query = "UPDATE tblVoucherPrice SET";
            query += " tblVoucherPrice.mIssuePrice  = " + _New_VoucherPrice;
            query += " FROM tblVoucherPrice INNER JOIN tblVoucherType ON tblVoucherType.lID = tblVoucherPrice.lVoucherTypeID";
            query += " WHERE nVoucherCode = '" + _VoucherBarcode+"'";

            var query_log = new StringBuilder();
            using (var db = new DbHelper())
            {
                query_log.Append("INSERT INTO tblManualVoucherLog (Req_No, User_No,FunctionName,Script)");
                query_log.Append(" VALUES (@ReqNo,null,@FunctionName,@Script)");
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@ReqNo", _ReqNo);
                    //db.AddParameter("@UserNo", _UserNo);
                    db.AddParameter("@FunctionName", "ChangeIssuePrice");
                    db.AddParameter("@Script", query);

                    db.ExecuteNonQuery(query, DbConnectionState.KeepOpen);

                    db.ExecuteNonQuery(query_log.ToString(), DbConnectionState.KeepOpen);

                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0008", ex));
                    return false;
                }
            }
            return result;
        }
#endregion

        #region "Change Quota"
        public  DataTable GetVoucherQuotaData(string _VoucherText)
        {

            var result = new DataTable();
            string query = "SELECT tblVoucherType.nVoucherCode,tblVoucherType.sDescription,";
            query += " ISNULL(tblVoucherType.VoucherType_intRedeemablep,0)AS Current_PaymentQty,";
            query += " ISNULL(tblVoucherType.VoucherTYpe_intRedeemableT,0) As Current_TicketQty,VoucherType_strRedeemableType FROM tblVoucherType";
            query += " WHERE VoucherType_strRedeemableType ='L'";
            query += " AND tblVoucherType.nVoucherCode ='" + _VoucherText + "'";
            using (var db = new DbHelper())
            {
                try
                {
                    result = db.ExecuteDataSet(query).Tables[0];
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0009", ex));
                }
            }
            return result;
        }
        public tbl_Voucher GetDataVoucherQuota_ByVoucherBarcode(string _VoucherBarcode)
        {
            tbl_Voucher result = new tbl_Voucher();
            string query = "SELECT tblVoucherType.nVoucherCode,tblVoucherType.sDescription,";
            query += " ISNULL(tblVoucherType.VoucherType_intRedeemablep,0)AS Current_PaymentQty,";
            query += " ISNULL(tblVoucherType.VoucherTYpe_intRedeemableT,0) As Current_TicketQty,VoucherType_strRedeemableType FROM tblVoucherType";
            query += " WHERE VoucherType_strRedeemableType ='L'";
            query += " AND tblVoucherType.nVoucherCode ='" + _VoucherBarcode + "'";
            using (var db = new DbHelper())
            {
                try
                {
                    db.AddParameter("@nVoucherCode", _VoucherBarcode);
                    var dt = db.ExecuteDataSet(query).Tables[0];

                    if (dt != null && dt.Rows.Count > 0)
                    {
                      double  _Current_PaymentQty = SF.GetDouble(dt.Rows[0]["Current_PaymentQty"]);
                      double    _Current_TicketQty= SF.GetDouble(dt.Rows[0]["Current_TicketQty"]);
                      double _sum_qty = _Current_PaymentQty + _Current_TicketQty;

                        result.nVoucherCode = SF.GetString(dt.Rows[0]["nVoucherCode"]);
                        result.sDescription = SF.GetString(dt.Rows[0]["sDescription"]);
                        result.Current_PaymentQty = _Current_PaymentQty;
                        result.Current_TicketQty = _Current_TicketQty;
                        result.strRedeemableType = SF.GetString(dt.Rows[0]["VoucherType_strRedeemableType"]);
                        result.sum_qty = _sum_qty;
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0007", ex));
                }
            }
            return result;
        }

        public bool UpdateVoucherQuotaDataPaymentQty_ByID(string _VoucherBarcode, string _New_Qty, string _ReqNo, string _UserNo)
        {
            bool result = true;
            string query = "UPDATE tblVoucherType SET";
            query += " VoucherType_intRedeemablep  = " + _New_Qty;
            query += " WHERE nVoucherCode = '" + _VoucherBarcode + "'";

            var query_log = new StringBuilder();
            using (var db = new DbHelper())
            {
                query_log.Append("INSERT INTO tblManualVoucherLog (Req_No, User_No,FunctionName,Script)");
                query_log.Append(" VALUES (@ReqNo,null,@FunctionName,@Script)");
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@ReqNo", _ReqNo);
                    //db.AddParameter("@UserNo", _UserNo);
                    db.AddParameter("@FunctionName", "ChangeQuota");
                    db.AddParameter("@Script", query);

                    db.ExecuteNonQuery(query, DbConnectionState.KeepOpen);

                    db.ExecuteNonQuery(query_log.ToString(), DbConnectionState.KeepOpen);

                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0008", ex));
                    return false;
                }
            }
            return result;
        }

        public bool UpdateVoucherQuotaDataTicketQty_ByID(string _VoucherBarcode, string _New_Qty, string _ReqNo, string _UserNo)
        {
            bool result = true;
            string query = "UPDATE tblVoucherType SET";
            query += " VoucherTYpe_intRedeemableT  = " + _New_Qty;
            query += " WHERE nVoucherCode = '" + _VoucherBarcode + "'";

            var query_log = new StringBuilder();
            using (var db = new DbHelper())
            {
                query_log.Append("INSERT INTO tblManualVoucherLog (Req_No, User_No,FunctionName,Script)");
                query_log.Append(" VALUES (@ReqNo,@UserNo,@FunctionName,@Script)");
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@ReqNo", _ReqNo);
                    db.AddParameter("@UserNo", _UserNo);
                    db.AddParameter("@FunctionName", "ChangeQuota");
                    db.AddParameter("@Script", query);

                    db.ExecuteNonQuery(query, DbConnectionState.KeepOpen);

                    db.ExecuteNonQuery(query_log.ToString(), DbConnectionState.KeepOpen);

                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0008", ex));
                    return false;
                }
            }
            return result;
        }

        #endregion

    }
}